package com.example.shangjiba;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

/**
 * 书籍数据库帮助类
 */
public class BookDatabaseHelper extends SQLiteOpenHelper {
    
    private static final String DATABASE_NAME = "books.db";
    private static final int DATABASE_VERSION = 1;
    
    private static final String TABLE_BOOKS = "books";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_BOOK_NO = "book_no";
    private static final String COLUMN_BOOK_NAME = "book_name";
    private static final String COLUMN_USER_EMAIL = "user_email";

    public BookDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_BOOKS + " (" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_BOOK_NO + " TEXT NOT NULL, " +
                COLUMN_BOOK_NAME + " TEXT NOT NULL, " +
                COLUMN_USER_EMAIL + " TEXT NOT NULL)";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOKS);
        onCreate(db);
    }

    // 添加书籍
    public long addBook(Book book) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_BOOK_NO, book.getBookNo());
        values.put(COLUMN_BOOK_NAME, book.getBookName());
        values.put(COLUMN_USER_EMAIL, book.getUserEmail());
        long id = db.insert(TABLE_BOOKS, null, values);
        db.close();
        return id;
    }

    // 删除书籍
    public int deleteBook(int bookId, String userEmail) {
        SQLiteDatabase db = this.getWritableDatabase();
        int result = db.delete(TABLE_BOOKS, 
                COLUMN_ID + " = ? AND " + COLUMN_USER_EMAIL + " = ?", 
                new String[]{String.valueOf(bookId), userEmail});
        db.close();
        return result;
    }

    // 修改书籍
    public int updateBook(Book book) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_BOOK_NO, book.getBookNo());
        values.put(COLUMN_BOOK_NAME, book.getBookName());
        int result = db.update(TABLE_BOOKS, values, 
                COLUMN_ID + " = ? AND " + COLUMN_USER_EMAIL + " = ?", 
                new String[]{String.valueOf(book.getId()), book.getUserEmail()});
        db.close();
        return result;
    }

    // 查询所有书籍（按用户）
    public List<Book> getAllBooks(String userEmail) {
        List<Book> books = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_BOOKS, null, 
                COLUMN_USER_EMAIL + " = ?", 
                new String[]{userEmail}, 
                null, null, COLUMN_ID + " DESC");
        
        if (cursor.moveToFirst()) {
            do {
                Book book = new Book(
                        cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_BOOK_NO)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_BOOK_NAME)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_USER_EMAIL))
                );
                books.add(book);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return books;
    }

    // 按书号查询
    public List<Book> searchByBookNo(String bookNo, String userEmail) {
        List<Book> books = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_BOOKS, null, 
                COLUMN_BOOK_NO + " LIKE ? AND " + COLUMN_USER_EMAIL + " = ?", 
                new String[]{"%" + bookNo + "%", userEmail}, 
                null, null, COLUMN_ID + " DESC");
        
        if (cursor.moveToFirst()) {
            do {
                Book book = new Book(
                        cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_BOOK_NO)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_BOOK_NAME)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_USER_EMAIL))
                );
                books.add(book);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return books;
    }

    // 按书名查询
    public List<Book> searchByBookName(String bookName, String userEmail) {
        List<Book> books = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_BOOKS, null, 
                COLUMN_BOOK_NAME + " LIKE ? AND " + COLUMN_USER_EMAIL + " = ?", 
                new String[]{"%" + bookName + "%", userEmail}, 
                null, null, COLUMN_ID + " DESC");
        
        if (cursor.moveToFirst()) {
            do {
                Book book = new Book(
                        cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_BOOK_NO)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_BOOK_NAME)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_USER_EMAIL))
                );
                books.add(book);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return books;
    }
}
